import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {
    public static void main(String[] args) {
        System.out.println(StudentDAO.queryAll());
    }
    //定义一个查询所有数据的方法
    public static List<Student> queryAll(){
        //查询数据
        //链接数据库
        ArrayList<Student> students = new ArrayList<Student>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            throw new RuntimeException();
        }
        String url = "jdbc:mysql://localhost:3306/studentManagement?" +
                "serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
        try {
            Connection con = DriverManager.getConnection(url, "root", "root");
            //写一个sql语句
            String sql = "select * from Student";
            //执行sql
            Statement stm = con.createStatement();
            //会得到结果集
            ResultSet rs = stm.executeQuery(sql);
            //处理结果集
            while(rs.next()){
                //id
                int id = rs.getInt("id");
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                String grade = rs.getString("grade");
                Student student = new Student(sno, sname, grade);
                students.add(student);
            }
            rs.close();
            stm.close();
            con.close();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return students;
    }
    public static List<Student> queryLike(String sno,String sname,String sgrade){
        ArrayList<Student> students = new ArrayList();
        //注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            throw new RuntimeException();
        }
        String url = "jdbc:mysql://localhost:3306/studentManagement?" +
                "serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
        try {
            //连接数据库
            Connection con = DriverManager.getConnection(url, "root", "root");
            StringBuffer sql = new StringBuffer("SELECT * FROM student WHERE 1=1 ");
            //判断传入的参数是否有效
            if(sno!=null&&!sno.isEmpty()){
                sql.append(" and sno like ?");
            }
            if(sname!=null&&!sname.isEmpty()){
                sql.append(" and sname like ?");
            }
            if(sgrade!=null&&!sgrade.isEmpty()){
                sql.append(" and sgrade like ?");
            }
            //sql的预编译
            String sqlString = sql.toString();
            PreparedStatement pstm = con.prepareStatement(sqlString);
            //进行占位？的位置
            int index = 1;
            if(sqlString.contains("sno")){
                pstm.setString(index++,"%"+sno+"%");
            }
            if(sqlString.contains("sname")){
                pstm.setString(index++,"%"+sname+"%");
            }
            if(sqlString.contains("sgrade")){
                pstm.setString(index++,"%"+sgrade+"%");
            }
            //会得到结果集
            ResultSet rs = pstm.executeQuery();
            //处理结果集
            while(rs.next()){
                //id
                int id = rs.getInt("id");
                String sno1 = rs.getString("sno");
                String sname1 = rs.getString("sname");
                String grade = rs.getString("grade");
                Student student = new Student(sno1, sname1, grade);
                students.add(student);
            }
            rs.close();
            pstm.close();
            con.close();
        }catch (Exception e){
            System.out.println(e.getMessage());
            throw new RuntimeException();
        }
        //连接数据库
        return students;
    }
    public static int add(Student s){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            throw new RuntimeException();
        }
        String url = "jdbc:mysql://localhost:3306/studentManagement?" +
                "serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
        try {
            Connection con = DriverManager.getConnection(url, "root", "root");
            //准备sql语句
            String sql = "insert into student values(NULL,?,?,?)";
            //获得执行失去了的对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //设置占位符
            pstm.setString(1, s.getSno());
            pstm.setString(2,s.getSname());
            pstm.setString(3,s.getSgrade());
            //执行sql
            int i = pstm.executeUpdate();
            //关闭资源
            pstm.close();
            con.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //删除函数
    public static int delete(String sno){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (Exception e){
            throw new RuntimeException();
        }
        String url = "jdbc:mysql://localhost:3306/studentManagement?" +
                "serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
        try {
            Connection con = DriverManager.getConnection(url, "root", "root");
            //准备sql
            String sql = "delete from student where sno=?";
            //执行sql对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //进行占位符
            pstm.setString(1,sno);
            //执行
            int i = pstm.executeUpdate();
            //释放资源
            pstm.close();
            con.close();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
